const xlsx = require('node-xlsx');
const fs = require('fs');
const path = require('path');

// 配置路径
const _file = path.join(__dirname, 'excel');
const _output = path.join(__dirname, 'result');

const headerIndex = -1; // 表头索引
const splitNumber = 100000; // 每个文件分割的行数
readExcelFiles(_file)
// 递归读取目录下所有xlsx文件
function readExcelFiles (dir) {
  const results = [];
  const files = fs.readdirSync(dir);

  files.forEach(file => {
    const fullPath = path.join(dir, file);
    const stat = fs.statSync(fullPath);

    if (stat.isDirectory()) {
      // 递归处理子目录
      results.push(...readExcelFiles(fullPath));
    } else if (path.extname(file) === '.xlsx' && !file.includes("~$")) {
      // 读取Excel文件
      try {
        const workSheets = xlsx.parse(fullPath);
        console.log(`成功读取: ${fullPath}`);
        splitExcel(workSheets, file)
      } catch (err) {
        console.error(`读取失败: ${fullPath}`, err);
      }
    }
  });

  return results;
}



function writeExcelFile (xlsxName, headers, data) {
  const newFileName = `${xlsxName}.xlsx`;
  const newFilePath = path.join(_output, newFileName);

  // 确保输出目录存在
  if (!fs.existsSync(_output)) {
    fs.mkdirSync(_output, { recursive: true });
  }

  // 构建新的工作表数据
  const buffer = xlsx.build([{ name: "sheet1", data: headers ? [headers, ...data] : data }]);

  // 写入新的Excel文件
  fs.writeFileSync(newFilePath, buffer);
  console.log(`已写入: ${newFilePath}`);
}
function splitExcel (workSheets, file) {
  let allData = {
    data: [],
    dataInfo: []
  };
  workSheets.forEach((sheet, sheetIndex) => {
    const sheetName = sheet.name || 'Sheet1';
    const rows = sheet.data;

    // 如果没有数据则跳过
    if (rows.length === 0) return;

    // 分割数据
    if (sheetIndex === 0) {
      allData.data = rows;
    } else {
      allData.data = allData.data.concat(rows.slice(headerIndex + 1))
    }

    allData.dataInfo.push({ name: sheetName, endIndex: allData.data.length - 1 });
    console.log("sheetName", sheetName, "行数", rows.length);
  });
  console.log(`共读取 ${allData.data.length} 行数据，开始分割...`);
  console.log(JSON.stringify(allData.dataInfo));
  // 处理分割逻辑
  for (let i = headerIndex > -1 ? headerIndex : 0; i < allData.data.length; i += splitNumber) {
    console.log(`正在处理数据行 ${i + 1} 到 ${Math.min(i + splitNumber, allData.data.length)}...`);
    const currentData = allData.data.slice(i, i + splitNumber);
    const fileCount = Math.floor(i / splitNumber) + 1;

    // 如果是第一次循环，获取表头
    const headers = headerIndex > -1 ? allData.data[headerIndex] : null;

    // 如果没有数据则跳过
    if (currentData.length === 0) {
      continue;
    }
    // 获取当前范围是在那个sheet区间
    let temp = allData.dataInfo.findIndex(info => i <= info.endIndex && i + splitNumber - 1 >= info.endIndex);
    let name = ""
    if (temp > -1) {
      name += allData.dataInfo[temp].name
      if (allData.dataInfo[temp + 1]?.name) {
        name = name + "-" + (allData.dataInfo[temp].endIndex % splitNumber) + "-" + (allData.dataInfo[temp + 1].name)
      }
    }

    let xlsxName = file.replace('.xlsx', '') + "~" + (temp > -1 ? name : allData.dataInfo.find(info => i <= info.endIndex && i + splitNumber - 1 <= info.endIndex)?.name) + `~${fileCount}`;

    // 写入Excel文件
    console.log(`正在写入${xlsxName} 文件...`);
    writeExcelFile(xlsxName, headers, currentData);
  }
}
// // 执行读取
// const allExcelData = readExcelFiles(_file);
// console.log(`共拆分 ${allExcelData.length} 个Excel文件`);